--    Program: 		CAPAREXT_CUSTOMER_LOCATION.sql
--    Description:  Extract AR Customer Location information
--    Revision:     1st     
--    Parameter:    1. set of books short name e.g. SOB_C999

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;
alter session set nls_date_format='YYYYMMDD HH24:MI:SS';

declare   
   l_processed_count		   number;
   l_rejected_count 		   number;
   l_err_msg				   varchar2(2000);
                    		
   l_utl_file       		   utl_file.file_type;
   l_filename       		   varchar2(255);
   l_pathname       		   varchar2(100);
                    		
   l_ngo_sob				   varchar2(150);
   l_ngo_sob_id            	   number;
                    		       
   cursor cur_location (p_sob_id number) is   
      select hl.* 
      from   hz_cust_acct_sites_all hcas,
             hz_party_sites hps,
      	     hz_locations hl,
      	     hr_organization_information hoi 
      where  hcas.party_site_id = hps.party_site_id
      and    hps.location_id = hl.location_id
      and    hcas.org_id = hoi.organization_id
      and    hoi.org_information_context = 'Operating Unit Information' 
      and    hoi.org_information3 = to_char(p_sob_id);

begin
   l_pathname := '/tmp';
   l_filename := 'CAPAR_CUSTOMER_LOCATION.dat';
   l_utl_file := utl_file.fopen(l_pathname, l_filename, 'w');
  
   l_processed_count := 0;
   l_rejected_count  := 0;
      
   l_ngo_sob := '&1';
   
   begin
    select set_of_books_id
    into   l_ngo_sob_id
    from   gl_sets_of_books 
    where  upper(short_name) = upper(l_ngo_sob);
  exception
     when others then
       dbms_output.put_line(l_ngo_sob||': set of books name not found');
  end;

   for cur in cur_location(l_ngo_sob_id) loop   	
	  	  
  	  utl_file.put_line(l_utl_file,
  	    cur.LOCATION_ID                   || chr(9) ||
        cur.LAST_UPDATE_DATE              || chr(9) ||
        cur.LAST_UPDATED_BY               || chr(9) ||
        cur.CREATION_DATE                 || chr(9) ||
        cur.CREATED_BY                    || chr(9) ||
        cur.LAST_UPDATE_LOGIN             || chr(9) ||
        cur.REQUEST_ID                    || chr(9) ||
        cur.PROGRAM_APPLICATION_ID        || chr(9) ||
        cur.PROGRAM_ID                    || chr(9) ||
        cur.PROGRAM_UPDATE_DATE           || chr(9) ||
        cur.WH_UPDATE_DATE                || chr(9) ||
        cur.ATTRIBUTE_CATEGORY            || chr(9) ||
        cur.ATTRIBUTE1                    || chr(9) ||
        cur.ATTRIBUTE2                    || chr(9) ||
        cur.ATTRIBUTE3                    || chr(9) ||
        cur.ATTRIBUTE4                    || chr(9) ||
        cur.ATTRIBUTE5                    || chr(9) ||
        cur.ATTRIBUTE6                    || chr(9) ||
        cur.ATTRIBUTE7                    || chr(9) ||
        cur.ATTRIBUTE8                    || chr(9) ||
        cur.ATTRIBUTE9                    || chr(9) ||
        cur.ATTRIBUTE10                   || chr(9) ||
        cur.ATTRIBUTE11                   || chr(9) ||
        cur.ATTRIBUTE12                   || chr(9) ||
        cur.ATTRIBUTE13                   || chr(9) ||
        cur.ATTRIBUTE14                   || chr(9) ||
        cur.ATTRIBUTE15                   || chr(9) ||
        cur.ATTRIBUTE16                   || chr(9) ||
        cur.ATTRIBUTE17                   || chr(9) ||
        cur.ATTRIBUTE18                   || chr(9) ||
        cur.ATTRIBUTE19                   || chr(9) ||
        cur.ATTRIBUTE20                   || chr(9) ||
        cur.GLOBAL_ATTRIBUTE_CATEGORY     || chr(9) ||
        cur.GLOBAL_ATTRIBUTE1             || chr(9) ||
        cur.GLOBAL_ATTRIBUTE2             || chr(9) ||
        cur.GLOBAL_ATTRIBUTE3             || chr(9) ||
        cur.GLOBAL_ATTRIBUTE4             || chr(9) ||
        cur.GLOBAL_ATTRIBUTE5             || chr(9) ||
        cur.GLOBAL_ATTRIBUTE6             || chr(9) ||
        cur.GLOBAL_ATTRIBUTE7             || chr(9) ||
        cur.GLOBAL_ATTRIBUTE8             || chr(9) ||
        cur.GLOBAL_ATTRIBUTE9             || chr(9) ||
        cur.GLOBAL_ATTRIBUTE10            || chr(9) ||
        cur.GLOBAL_ATTRIBUTE11            || chr(9) ||
        cur.GLOBAL_ATTRIBUTE12            || chr(9) ||
        cur.GLOBAL_ATTRIBUTE13            || chr(9) ||
        cur.GLOBAL_ATTRIBUTE14            || chr(9) ||
        cur.GLOBAL_ATTRIBUTE15            || chr(9) ||
        cur.GLOBAL_ATTRIBUTE16            || chr(9) ||
        cur.GLOBAL_ATTRIBUTE17            || chr(9) ||
        cur.GLOBAL_ATTRIBUTE18            || chr(9) ||
        cur.GLOBAL_ATTRIBUTE19            || chr(9) ||
        cur.GLOBAL_ATTRIBUTE20            || chr(9) ||
        cur.ORIG_SYSTEM_REFERENCE         || chr(9) ||
        cur.COUNTRY                       || chr(9) ||
        cur.ADDRESS1                      || chr(9) ||
        cur.ADDRESS2                      || chr(9) ||
        cur.ADDRESS3                      || chr(9) ||
        cur.ADDRESS4                      || chr(9) ||
        cur.CITY                          || chr(9) ||
        cur.POSTAL_CODE                   || chr(9) ||
        cur.STATE                         || chr(9) ||
        cur.PROVINCE                      || chr(9) ||
        cur.COUNTY                        || chr(9) ||
        cur.ADDRESS_KEY                   || chr(9) ||
        cur.ADDRESS_STYLE                 || chr(9) ||
        cur.VALIDATED_FLAG                || chr(9) ||
        cur.ADDRESS_LINES_PHONETIC        || chr(9) ||
        cur.APARTMENT_FLAG                || chr(9) ||
        cur.PO_BOX_NUMBER                 || chr(9) ||
        cur.HOUSE_NUMBER                  || chr(9) ||
        cur.STREET_SUFFIX                 || chr(9) ||
        cur.APARTMENT_NUMBER              || chr(9) ||
        cur.SECONDARY_SUFFIX_ELEMENT      || chr(9) ||
        cur.STREET                        || chr(9) ||
        cur.RURAL_ROUTE_TYPE              || chr(9) ||
        cur.RURAL_ROUTE_NUMBER            || chr(9) ||
        cur.STREET_NUMBER                 || chr(9) ||
        cur.BUILDING                      || chr(9) ||
        cur.FLOOR                         || chr(9) ||
        cur.SUITE                         || chr(9) ||
        cur.ROOM                          || chr(9) ||
        cur.POSTAL_PLUS4_CODE             || chr(9) ||
        cur.TIME_ZONE                     || chr(9) ||
        cur.OVERSEAS_ADDRESS_FLAG         || chr(9) ||
        cur.POST_OFFICE                   || chr(9) ||
        cur.POSITION                      || chr(9) ||
        cur.DELIVERY_POINT_CODE           || chr(9) ||
        cur.LOCATION_DIRECTIONS           || chr(9) ||
        cur.ADDRESS_EFFECTIVE_DATE        || chr(9) ||
        cur.ADDRESS_EXPIRATION_DATE       || chr(9) ||
        cur.ADDRESS_ERROR_CODE            || chr(9) ||
        cur.CLLI_CODE                     || chr(9) ||
        cur.DODAAC                        || chr(9) ||
        cur.TRAILING_DIRECTORY_CODE       || chr(9) ||
        cur.LANGUAGE                      || chr(9) ||
        cur.LIFE_CYCLE_STATUS             || chr(9) ||
        cur.SHORT_DESCRIPTION             || chr(9) ||
        cur.DESCRIPTION                   || chr(9) ||
        cur.CONTENT_SOURCE_TYPE           || chr(9) ||
        cur.LOC_HIERARCHY_ID              || chr(9) ||
        cur.SALES_TAX_GEOCODE             || chr(9) ||
        cur.SALES_TAX_INSIDE_CITY_LIMITS  || chr(9) ||
        cur.FA_LOCATION_ID                || chr(9) ||
        --cur.GEOMETRY                      || chr(9) ||
        cur.OBJECT_VERSION_NUMBER         || chr(9) ||
        cur.CREATED_BY_MODULE             || chr(9) ||
        cur.APPLICATION_ID                || chr(9) ||
        cur.TIMEZONE_ID                   || chr(9) ||
        cur.GEOMETRY_STATUS_CODE          || chr(9) ||
        cur.ACTUAL_CONTENT_SOURCE
	   );
       	        
	  l_processed_count := l_processed_count + 1;
   end loop;
   
   l_err_msg := '';
   if l_processed_count = 0 then   
     l_rejected_count := l_rejected_count + 1;
     l_err_msg := l_ngo_sob || ': Customer Location not found';
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPAREXT_CUSTOMER_LOCATION', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;

   utl_file.fclose(l_utl_file);
exception 
  when others then
    dbms_output.put_line(sqlerrm);
end;
/     


--exit;
